import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;



public class VideoDBComm {

    private static Connection conn;
    private PreparedStatement videoStatement;
    private Statement stmnt;
    ResultSet rs;

    VideoDBComm(){
        
    }

    protected void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://aasudrl1.db.6601164.hostedresource.com/aasudrl1", "aasudrl1", "Csci1301");
            System.out.print("Established Connection: " + conn.isClosed());
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
                System.out.println("Created Statement");
            }
            
            if (!conn.getMetaData().getTables(null, null, "video", null).next()) {
                stmnt.execute("CREATE TABLE video (name VARCHAR(10), frame BLOB)");
                stmnt.execute("INSERT INTO video(name, frame) VALUES ('frame',NULL)");
            }
            System.out.print("Created table video and inserted frame");
       

        }catch(SQLException ex){

        }catch(ClassNotFoundException ex){
            
        }
    }
    
      protected void clear() {
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            stmnt.execute("DELETE FROM video;");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

     protected void disconnect() {
        clear();
        try {
            if (!conn.isClosed()) {
                stmnt.close();
                conn.close();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

      protected void uploadFrame(File image) {
        try {
            videoStatement = conn.prepareStatement("UPDATE VIDEO SET frame=? WHERE name='frame'");
            
            if (image.length() > 0) {
                FileInputStream fis = new FileInputStream(image);

                videoStatement.setBinaryStream(1, fis, (int) image.length());
                videoStatement.executeUpdate();

                fis.close();
                videoStatement.close();
                System.out.println("Updated");
            }
        
        } catch (SQLException ex) {
            System.out.println("Socket Connection Reset -- No Update");
        } catch (FileNotFoundException ex){
            ex.printStackTrace();
        }catch (IOException ex){
            ex.printStackTrace();
        }
    }


}